/****************************** Module Header ******************************\
 * Module Name:  DatabaseHelper.cs
 * Module Version:  1.0.0 (2011-11-1)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to operate database. It supplies public method to execute 
 * a sql command or get a common connection or get the database schema infomation.
 * 
 * log:2012-8-14 by sutroon
 * 1.string[] ConnectionInfo 替换成 ConnectionStringSettings ConnectionSetting
 * 2.删除ProviderName和ConnectionInfo等属性
 * log:2012-11-27 by sutroon
 * 重构,由DBHelper改为DatabaseHelper
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;

namespace UCF.Core.Data
{
    /// <summary>
    /// 数据库操纵类
    /// <para>提供获取连接对象、执行命令、获取数据库架构信息等方法</para>
    /// </summary>
    public class DatabaseHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 数据源提供程序名称
        /// </summary>
        public string ProviderName { get; set; }

        /// <summary>
        /// 超时时间(s)
        /// </summary>
        public int Timeout { get; set; }

        /// <summary>
        /// 构造方法
        /// </summary>
        public DatabaseHelper(string connectionString, string providerName)
        {
            this.ConnectionString = connectionString;
            this.ProviderName = providerName;
        }

        #region 数据库普通操纵方法

        /// <summary>
        /// 执行一条命令并返回受影响的行数
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, DbParameter[] paras = null)
        {
            if (string.IsNullOrEmpty(commandText)) return 0;
            int intOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (Timeout > 0) cmd.CommandTimeout = Timeout;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    conn.Open();
                    intOut = cmd.ExecuteNonQuery();
                }
            }
            return intOut;
        }

        /// <summary>
        /// 执行命令并返回一个数据表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, DbParameter[] paras = null)
        {
            DataTable dt = new DataTable();
            if (string.IsNullOrEmpty(commandText)) return dt;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (Timeout > 0) cmd.CommandTimeout = Timeout;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    DbDataAdapter dbda = GetDbProviderFactory().CreateDataAdapter();
                    dbda.SelectCommand = cmd;
                    conn.Open();
                    dbda.Fill(dt);
                }
            }
            return dt;
        }
        /// <summary>
        /// 2013-6-19 by sutroon
        /// </summary>
        /// <param name="fromsql"></param>
        /// <param name="orderby"></param>
        /// <param name="fields"></param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable ExecutePagingDataTable(string fromsql, string orderby, string fields, out int recordcount, out int pagecount, int pageid = 1, int pagesize = 20)
        {
            recordcount = Convert.ToInt32(this.GetValue(string.Format("select count(0) {0};", fromsql)));
            if (recordcount == 0)
            {
                pagecount = 0;
                return new DataTable();
            }
            pagecount = recordcount / pagesize;
            if (recordcount % pagesize > 0) pagecount += 1;
            return this.ExecuteDataTable(this.GetSelectPagingSqlStatement(fromsql, orderby, fields, pageid, pagesize));
        }
        /// <summary>
        /// 获取分页查询语句
        /// <para>2012-10-16</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="orderby"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public string GetSelectPagingSqlStatement(string fromsql, string orderby, string fields, int pageIndex, int pageSize)
        {
            if (!orderby.Trim().ToLower().StartsWith("order by ")) orderby = " order by " + orderby;
            if (pageIndex == 1) return "select top {0} {1} {2} {3}".Fmt(pageSize, fields, fromsql, orderby);
            int a = (pageIndex - 1) * pageSize + 1, b = pageIndex * pageSize;
            return "select {0} from(select row_number() over ({1}) as row_num,{0} {2}) as page_table where row_num between {3} and {4};".Fmt(fields, orderby, fromsql, a, b);
        }

        public object GetValue(string sql)
        {
            using (DbCommand cmd = this.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = sql;
                if (Timeout > 0) cmd.CommandTimeout = Timeout;
                cmd.Connection.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        return dr[0];
                    }
                }
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
            return null;
        }

        /// <summary>
        /// 递归获取分类数据表,数据主要用于DropDownList控件
        /// </summary>
        /// <param name="sqlQuery">查询语句，如<para>select ID,Name from catalog where parentid={0} and innercat='album' order by Ordinal</para></param>
        /// <param name="parentID">父编号</param>
        /// <param name="dataTable">用户填充数据的空数据表</param>
        /// <param name="paddingChar">名称前面分级填充的字符，如 '-'</param>
        /// <param name="loopCount">递归次数,默认值是0</param>
        public void ExecuteTreeList(string sqlQuery, string parentID, ref DataTable dataTable, char paddingChar, int loopCount)
        {
            bool isTree = false;
            string sqlSearch = sqlQuery;
            if (sqlQuery.IndexOf('{') > -1)
            {
                isTree = true;
                sqlSearch = sqlQuery.Replace("{0}", parentID);
            }
            if (dataTable.Columns.Count == 0)
            {
                dataTable.Columns.Add("ID");
                dataTable.Columns.Add("Name");
                dataTable.Columns.Add("Level");
            }
            DataRow dataRow;

            using (DbConnection conn = GetDbConnection())
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlSearch;
                if (Timeout > 0) cmd.CommandTimeout = Timeout;
                conn.Open();
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        dataRow = dataTable.NewRow();
                        dataRow[0] = dr[0].ToString();
                        dataRow[1] = string.Empty.PadRight(loopCount * 2, paddingChar) + dr[1].ToString();
                        dataRow[2] = loopCount.ToString();
                        dataTable.Rows.Add(dataRow);
                        if (isTree) ExecuteTreeList(sqlQuery, dr[0].ToString(), ref dataTable, paddingChar, loopCount+1);
                    }
                }
            }
        }

        /// <summary>
        /// 获取当前节点的所有子节点(包含节点关联的元素)和递归父节点树形列表
        /// <para>2013-9-9 by sutroon</para>
        /// <para>例如：ExecuteTreeXml("Select ID, Name from tUSerDepartment where ParentID='{0}'", "093F207D-634B-4717-AB50-91A21F035083", "Select AgentID, Name from tUserMember where DepartmentID='{0}'", "select ID, Name, ParentID from tUserDepartment where ID='{0}'"));</para>
        /// </summary>
        /// <param name="sqlfmt"></param>
        /// <param name="pid"></param>
        /// <param name="sqlfmtItems"></param>
        /// <param name="sqlfmtParent"></param>
        /// <returns></returns>
        public string ExecuteTreeXml(string sqlfmt, string pid, string sqlfmtItems = "", string sqlfmtParent = "")
        {
            StringBuilder sb = new StringBuilder(), sbChild = new StringBuilder(), sbParent = new StringBuilder();
            sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?><root>");
            ExecuteTreeXmlForChild_Func(sqlfmt, pid, ref sbChild, sqlfmtItems);
            if (string.IsNullOrEmpty(sqlfmtParent))
            {
                sb.Append(sbChild.ToString());
            }
            else
            {
                sbParent.Append(sbChild.ToString());
                ExecuteTreeXmlForParent_Func(sqlfmtParent, pid, ref sbParent);
                sb.Append(sbParent.ToString());
            }
            sb.Append("</root>");
            return sb.ToString();
        }
        private void ExecuteTreeXmlForChild_Func(string sqlfmt, string pid, ref StringBuilder sb, string sqlfmtItems = "")
        {
            DataTable dtItems = ExecuteDataTable(string.Format(sqlfmtItems, pid));
            if (dtItems.Rows.Count > 0)
            {
                foreach (DataRow r in dtItems.Rows)
                {
                    sb.AppendFormat("<node {0} />", ToAttributeString(r));
                }
            }
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.AppendFormat("<node {0}>", ToAttributeString(r));
                    ExecuteTreeXmlForChild_Func(sqlfmt, r[0].ToString(), ref sb, sqlfmtItems);
                    sb.Append("</node>");
                }
            }
        }
        private void ExecuteTreeXmlForChild_Func2(string sqlfmt, string pid, ref StringBuilder sb, string sqlfmtItems = "")
        {
            DataTable dt2;
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.AppendFormat("<node {0}>", ToAttributeString(r));
                    if (!string.IsNullOrEmpty(sqlfmtItems))
                    {
                        dt2 = ExecuteDataTable(string.Format(sqlfmtItems, r[0]));
                        if (dt2.Rows.Count > 0)
                        {
                            foreach (DataRow r2 in dt2.Rows)
                            {
                                sb.AppendFormat("<node {0} />", ToAttributeString(r2));
                            }
                        }
                    }
                    ExecuteTreeXmlForChild_Func(sqlfmt, r[0].ToString(), ref sb, sqlfmtItems);
                    sb.Append("</node>");
                }
            }
        }
        private string ToAttributeString(DataRow r, string splitor = " ", string symbol = "=")
        {
            StringBuilder sb = new StringBuilder();
            foreach (DataColumn c in r.Table.Columns)
            {
                sb.AppendFormat("{0}{1}{2}\"{3}\"", splitor, c.ColumnName, symbol, r[c]);
            }
            return sb.Length > splitor.Length ? sb.Remove(0, splitor.Length).ToString() : sb.ToString();
        }
        public void ExecuteTreeXmlForParent_Func(string sqlfmt, string pid, ref StringBuilder sb)
        {
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.Insert(0, string.Format("<node ID=\"{0}\" Name=\"{1}\">", r[0], r[1]));
                    ExecuteTreeXmlForParent_Func(sqlfmt, r[2].ToString(), ref sb);
                    sb.Append("</node>");
                }
            }
        }

        /// <summary>
        /// 获取指定行的所有值的列表
        /// </summary>
        /// <typeparam name="V">指定行的数据类型</typeparam>
        /// <param name="sqlQuery">查询语句</param>
        /// <returns></returns>
        public IList<V> GetColumnValues<V>(string sqlQuery)
        {
            IDataReader idr = this.ExecuteDataTable(sqlQuery).CreateDataReader();
            IList<V> list = new List<V>();
            while (idr.Read())
            {
                list.Add((V)Convert.ChangeType(idr[0], typeof(V)));
            }
            return list;
        }

        /// <summary>
        /// 导出Sql数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public string ExportDataAsSql(string tableName, string condition = "")
        {
            StringBuilder sb = new StringBuilder();
            using (DbDataReader ddr = this.ExecuteDataTable("select * from {0} {1}".Fmt(tableName, Sql_SetConditionText(condition))).CreateDataReader())
            {
                if (!ddr.HasRows) return "";

                StringBuilder sbFields;
                StringBuilder sbValues;

                sb.AppendLine();
                sb.AppendLine("-- set identity_insert [" + tableName + "] on");
                sb.AppendLine();
                while (ddr.Read())
                {
                    sbValues = new StringBuilder();
                    sbFields = new StringBuilder();
                    for (int i = 0; i < ddr.FieldCount; i++)
                    {
                        if (ddr[i] == DBNull.Value) continue;
                        sbFields.AppendFormat("[{0}],", ddr.GetName(i));
                        string s = FieldTypeToClrTypeName(ddr.GetDataTypeName(i));
                        switch (FieldTypeToClrTypeName(ddr.GetDataTypeName(i), false))
                        {
                            case "string":
                            case "DateTime":
                            case "xml":
                            case "image":
                            case "Guid":
                                sbValues.AppendFormat("'{0}',", ddr[i]);
                                break;
                            case "bool":
                                sbValues.AppendFormat("{0},", (ddr[i].ToString() == "True" ? 1 : 0));
                                break;
                            default:
                                sbValues.AppendFormat("{0},", ddr[i]);
                                break;
                        }
                    }
                    sb.AppendFormat("insert into [{0}] ({1}) values ({2});", tableName, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
                    sb.AppendLine();
                    sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        /// <summary>
        /// 导出所有Sql数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public Dictionary<string, string> ExportAllDataAsDictionary(params string[] tableNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            DbDataReader ddr;
            StringBuilder sb, sbFields, sbValues;
            foreach (string s in tableNames)
            {
                using (ddr = this.ExecuteDataTable("select * from {0}".Fmt(s)).CreateDataReader())
                {
                    if (!ddr.HasRows) continue;

                    sb = new StringBuilder();

                    sb.AppendLine();
                    sb.AppendLine("-- set identity_insert [" + s + "] on");
                    sb.AppendLine();
                    while (ddr.Read())
                    {
                        sbValues = new StringBuilder();
                        sbFields = new StringBuilder();
                        for (int i = 0; i < ddr.FieldCount; i++)
                        {
                            if (ddr[i] == DBNull.Value) continue;
                            sbFields.AppendFormat("[{0}],", ddr.GetName(i));
                            switch (FieldTypeToClrTypeName(ddr.GetDataTypeName(i), false))
                            {
                                case "string":
                                case "DateTime":
                                case "xml":
                                case "image":
                                case "Guid":
                                    sbValues.AppendFormat("'{0}',", ddr[i]);
                                    break;
                                case "bool":
                                    sbValues.AppendFormat("{0},", (ddr[i].ToString() == "True" ? 1 : 0));
                                    break;
                                default:
                                    sbValues.AppendFormat("{0},", ddr[i]);
                                    break;
                            }
                        }
                        sb.AppendFormat("insert into [{0}] ({1}) values ({2});", s, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
                        sb.AppendLine();
                        sb.AppendLine();
                    }
                    dict.Add(s, sb.ToString());
                }
            }
            return dict;
        }
        #endregion

        #region 获取基础对象方法

        /// <summary>
        /// 获取通用数据库连接对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbConnection GetDbConnection()
        {
            DbProviderFactory dbpf = DbProviderFactories.GetFactory(ProviderName);
            DbConnection conn = dbpf.CreateConnection();
            conn.ConnectionString = ConnectionString;
            return conn;
        }

        /// <summary>
        /// 获取通用数据库命令对象
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbCommand GetDbCommand()
        {
            return GetDbProviderFactory().CreateCommand();
        }

        /// <summary>
        /// 获取通用数据连接工厂对象
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbProviderFactory GetDbProviderFactory()
        {
            return DbProviderFactories.GetFactory(ProviderName);
        }
        #endregion

        #region 获取SQL命令语句方法

        /// <summary>
        /// 获取对应数据库的自动增长字段语句
        /// </summary>
        /// <returns></returns>
        public static string Sql_GetAutoIncrementText()
        {
            return "identity(1,1)";
        }

        /// <summary>
        /// 获取对应数据库前N条记录的语句
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">要查找的字段，如 title,content,*等</param>
        /// <param name="condition">查询条件</param>
        /// <param name="count">记录数量</param>
        /// <returns></returns>
        public static string Sql_GetSelectTopNRecordText(string tableName, string fields = "*", string condition = "", int count = 1)
        {
            return "select top {3} {0} from {1} {2}".Fmt(fields, tableName, condition, count);
        }

        /// <summary>
        /// 获取删除表命令
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static string Sql_GetDropTableText(string tablename)
        {
            return string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) {1}DROP TABLE [dbo].[{0}];{1}".Fmt(tablename, Environment.NewLine));
        }

        /// <summary>
        /// 设置查询条件语句,建议查询条件无需带where关键词
        /// <para>包含符号=, is , like ,&gt;,&lt;,!&gt;等关键词自动补齐 where 关键词</para>
        /// <para>不包含符号且不带where 自动补齐 order by关键词</para>
        /// <para>强烈推荐使用符号：^=[order by],?=[where]来提高执行效率</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static string Sql_SetConditionText(string condition)
        {
            condition = condition.Trim();
            if (condition.Length == 0) return "";
            int iSpace = condition.IndexOf(' ');
            bool hasSymbol = false;
            char symbol = condition[0];
            if (symbol == '?') return " where {0} ".Fmt(condition.Substring(1));
            if (symbol == '^') return " order by {0} ".Fmt(condition.Substring(1));
            foreach (string s in new string[] { "=", ">", "<", "!", " is ", " like ", " in " })
            {
                if (condition.ToLower().IndexOf(s) > -1) hasSymbol = true;
            }
            if (iSpace == -1)
            {
                return hasSymbol ? " where {0} ".Fmt(condition) : " order by {0} ".Fmt(condition);
            }
            string word = condition.Substring(0, iSpace).ToLower();
            if (word == "where" || word == "order")
            {
                return condition;
            }
            return hasSymbol ? " where {0} ".Fmt(condition) : " order by {0} ".Fmt(condition);
        }
        public static string Sql_SetSqlCondition(string where, string orderby)
        {
            StringBuilder sb = new StringBuilder();
            if (where.Length > 0) sb.AppendFormat("where {0}", where);
            if (orderby.Length > 0) sb.AppendFormat("order by {0}", orderby);
            return sb.ToString();
        }

        /// <summary>
        /// 设置数量限制
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static string SetLimit(string sql, int count)
        {
            sql = sql.TrimStart();
            if (sql.Substring(0, 7).ToLower() != "select ") return sql;
            return sql.Insert(6, " top {0}".Fmt(count));
        }

        #endregion

        #region 其他转换操作

        /// <summary>
        /// 托管环境数据库类型名称转换为产品环境数据库类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="clrTypeName"></param>
        /// <returns></returns>
        public static string FieldTypeNameToDbTypeName(string clrTypeName)
        {
            clrTypeName = clrTypeName.ToLower();
            switch (clrTypeName)
            {
                case "int32": return "int";
                case "string": return "nvarchar(MAX)";
                case "int16": return "smallint";
                case "boolean": return "bit";
                case "guid": return "uniqueidentifier";
                case "datetime": return "datetime";
                case "decimal": return "money";
                case "int64": return "bigint";
                case "byte[]": return "image";
                case "single": return "real";
                case "double": return "float";
                case "datetimeoffset": return "datetimeoffset(7)";
                case "timespan": return "time(7)";
                case "system.xml.linq.xelement": return "xml";
            }
            return clrTypeName;
        }

        /// <summary>
        /// 托管环境数据库数据类型转换成C#数据类型
        /// </summary>
        /// <param name="fieldTypeName">数据库字段类型</param>
        /// <param name="isNullable">是否允许null</param>
        /// <returns>C#数据类型</returns>
        public static string FieldTypeToClrTypeName(string fieldTypeName, bool isNullable = true)
        {
            string sOut = fieldTypeName;
            switch (fieldTypeName.ToLower())
            {
                case "string":
                    sOut = "string";
                    break;
                case "int32":
                    sOut = isNullable ? "int?" : "int";
                    break;
                case "int16":
                    sOut = isNullable ? "short?" : "short";
                    break;
                case "byte":
                    sOut = isNullable ? "byte?" : "byte";
                    break;
                case "datetime":
                    sOut = isNullable ? "DateTime?" : "DateTime";
                    break;
                case "boolean":
                    sOut = isNullable ? "bool?" : "bool";
                    break;
                case "decimal":
                    sOut = isNullable ? "decimal?" : "decimal";
                    break;
                case "double":
                    sOut = isNullable ? "double?" : "double";
                    break;
                case "guid":
                    sOut = isNullable ? "Guid?" : "Guid";
                    break;
                case "byte[]":
                    sOut = "byte[]";
                    break;
                case "int64":
                    sOut = isNullable ? "long?" : "long";
                    break;
            }
            return string.Format("{0}", sOut);
        }

        /// <summary>
        /// C#数据类型转换为托管环境数据库数据类型
        /// </summary>
        /// <param name="clrType"></param>
        /// <returns></returns>
        public static DbType ClrTypeToDbType(Type clrType)
        {
            if (clrType.IsGType<int>()) return DbType.Int32;
            if (clrType == typeof(string) || clrType.IsGType<DateTime>()) return DbType.String;
            if (clrType.IsGType<double>()) return DbType.Double;
            if (clrType.IsGType<bool>()) return DbType.Boolean;
            if (clrType.IsGType<short>()) return DbType.Int16;
            if (clrType.IsGType<Guid>()) return DbType.Guid;
            if (clrType.IsGType<decimal>()) return DbType.Decimal;
            if (clrType == typeof(System.Xml.Linq.XElement)) return DbType.Xml;
            if (clrType == typeof(byte[])) return DbType.Binary;
            if (clrType == typeof(object)) return DbType.Object;
            if (clrType.IsGType<long>()) return DbType.Int64;
            return DbType.String;
        }

        /// <summary>
        /// C#数据类型转为SQL数据类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string ClrTypeToDbTypeName(Type type)
        {
            if (type.IsGType<int>()) return "int";
            if (type == typeof(string)) return "nvarchar";
            if (type.IsGType<short>()) return "smallint";
            if (type.IsGType<DateTime>()) return "datetime";
            if (type.IsGType<bool>()) return "bit";
            if (type.IsGType<Guid>()) return "uniqueidentifier";
            if (type == typeof(byte[])) return "image";
            if (type.IsGType<double>()) return "float";
            if (type.IsGType<decimal>()) return "money";
            if (type == typeof(System.Xml.Linq.XElement)) return "xml";
            if (type.IsGType<byte>()) return "tinyint";
            if (type.IsGType<long>()) return "bigint";
            if (type.IsGType<TimeSpan>()) return "time(7)";
            if (type.IsGType<DateTimeOffset>()) return "datetimeoffset(7)";
            if (type.IsGType<Single>()) return "real";
            return type.ToString().ToLower();
        }
        /// <summary>
        /// 格式化为指定数据库产品的sql命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string FormatSql(string sql)
        {
            return sql;
        }

        /// <summary>
        /// 获取指定的数据库产品的连接提供程序
        /// </summary>
        /// <param name="dbProduct"></param>
        /// <returns></returns>
        public static string GetProviderName(string dbProduct)
        {
            DatabaseProduct p;
            Enum.TryParse<DatabaseProduct>(dbProduct, out p);
            switch (p)
            {
                case DatabaseProduct.MySQL:
                    return "MySql.Data.MySqlClient";
            }
            return "System.Data.SqlClient";
        }

        /// <summary>
        /// 获取数据库产品名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public static DatabaseProduct GetDatabaseProduct(string providerName)
        {
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    return DatabaseProduct.MsSQL;
                case "MySql.Data.MySqlClient":
                    return DatabaseProduct.MySQL;
            }
            return DatabaseProduct.MsSQL;
        }

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <param name="dbProduct"></param>
        /// <param name="serverName"></param>
        /// <param name="loginID"></param>
        /// <param name="password"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        public static string GetConnectionString(string serverName, string loginID, string password, string database = "")
        {
            return "server={0};uid={1};pwd={2};{3};pooling=true;min pool size=10;max pool size=300;connection lifetime=0;".Fmt(serverName, loginID, password, database.Length > 0 ? "database={0};".Fmt(database) : "");
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool TestConnection(out string message)
        {
            using (DbConnection conn = GetDbConnection())
            {
                try
                {
                    conn.Open();
                    message = "连接成功";
                    return true;
                }
                catch (DbException e)
                {
                    message = e.Message;
                    return false;
                }
            }
        }
        #endregion

        #region 存储过程操纵方法

        /// <summary>
        /// 执行存储过程,返回新的参数值集合
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public Dictionary<string, object> ExecuteProcedureNonQuery(string procedureName, params DbParameter[] dbParameters)
        {
            int iOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.Parameters.AddRange(dbParameters);
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.CommandText = procedureName;
                conn.Open();
                iOut = oCmd.ExecuteNonQuery();
            }
            return dbParameters.ToDictionary();
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="outParameters"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, ref Dictionary<string, object> outParameters, params DbParameter[] dbParameters)
        {
            DataTable oDt = new DataTable();
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = procedureName;
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.Parameters.AddRange(dbParameters);
                DbDataAdapter oDa = GetDbProviderFactory().CreateDataAdapter();
                oDa.SelectCommand = oCmd;
                conn.Open();
                oDa.Fill(oDt);
            }
            if (outParameters == null) return oDt;
            foreach (DbParameter para in dbParameters)
            {
                outParameters[para.ParameterName] = para.Value;
            }
            return oDt;
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, params DbParameter[] dbParameters)
        {
            Dictionary<string, object> dict = null;
            return this.ExecuteProcedureDataTable(procedureName, ref dict, dbParameters);
        }

        #endregion

        #region 事务操作

        /// <summary>
        /// 执行一条命令并返回受影响的行数
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQueryT(string commandText, DbParameter[] paras = null)
        {
            if (string.IsNullOrEmpty(commandText)) return 0;
            int intOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    cmd.Transaction = trans;
                    try
                    {
                        intOut = cmd.ExecuteNonQuery();
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        trans.Dispose();
                    }
                }
            }
            return intOut;
        }
        #endregion

    }
}
